package com.platform.metamodel;

import com.google.common.collect.Lists;
import org.apache.metamodel.UpdateSummary;
import org.apache.metamodel.UpdateableDataContext;
import org.apache.metamodel.create.CreateTable;
import org.apache.metamodel.jdbc.JdbcDataContext;
import org.apache.metamodel.modify.ModifyTable;
import org.apache.metamodel.schema.ColumnType;
import org.apache.metamodel.schema.MutableColumn;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
 * @author liujizheng
 * @version 1.0
 * @ClassName Example
 * @Description 示例
 * @date 2021/8/4 15:47
 */
public class Example {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        // 创建表示例
        createTableExp1();
        createTableExp2();
        createTableExp3();

        // 修改表结构示例
        modifyTableExp();
    }

    private static void modifyTableExp() throws SQLException, ClassNotFoundException {
        UpdateableDataContext context = new JdbcDataContext(getDbConnection());

        // 方案名称或数据库名称
        String schemaName = "public";
        // 表名
        String tableName = "test";

        // 新增字段
        MutableColumn addColumn = new MutableColumn("nikename");
        addColumn.setType(ColumnType.STRING);
        addColumn.setColumnSize(16);
        UpdateSummary summary = context.executeUpdate(ModifyTable.addColumn(context, schemaName, tableName, addColumn));

        // 修改字段类型长度
        MutableColumn modifyColumn = new MutableColumn("name");
        modifyColumn.setType(ColumnType.STRING);
        modifyColumn.setColumnSize(18);
        summary = context.executeUpdate(ModifyTable.modifyColumn(context, schemaName, tableName, modifyColumn));

        // 修改字段名
        MutableColumn renameColumn = new MutableColumn("name");
        renameColumn.setType(ColumnType.STRING);
        renameColumn.setColumnSize(18);
        summary = context.executeUpdate(ModifyTable.renameColumn(context, schemaName, tableName, renameColumn, "firstname"));

        // 删除字段
        summary = context.executeUpdate(ModifyTable.dropColumn(context, schemaName, tableName, "firstname"));
    }

    private static void createTableExp3() throws SQLException, ClassNotFoundException {

        // 方案名称或数据库名称
        String schemaName = "public";
        // 表名
        String tableName = "test";

        MtTable mtTable = new MtTable(tableName,"用户信息",schemaName);

        MtField userId = new MtField("userId","TEXT","STRING",1,true);
        MtField userName = new MtField("userName","VARCHAR","STRING",2,36,false);
        MtField birth = new MtField("birth","DATE","DATE",3,false);
        MtField level = new MtField("level","INT","NUMBER",4,false);
        MtField balance = new MtField("balance","DECIMAL","NUMBER",5,10,2);
        MtField createDate = new MtField("createDate","TIMESTAMP","DATE",6,false);

        mtTable.addField(userId).addField(userName).addField(birth).addField(level).addField(balance).addField(createDate);

        UpdateableDataContext dataContext = new JdbcDataContext(getDbConnection());
        CreateTable createTable = new CreateTable(schemaName, mtTable.getTableName(), mtTable.getMutableColumns());
        UpdateSummary summary = dataContext.executeUpdate(createTable);
    }

    private static void createTableExp2() throws SQLException, ClassNotFoundException {
        UpdateableDataContext context = new JdbcDataContext(getDbConnection());

        // 方案名称或数据库名称
        String schemaName = "public";
        // 表名
        String tableName = "test";

        CreateTable createTable = new CreateTable(schemaName, tableName);

        // 创建字段
        MutableColumn column = new MutableColumn();
        column.setName("id");
        column.setType(ColumnType.VARCHAR);
        column.setNativeType("VARCHAR");
        column.setColumnSize(32);
        column.setPrimaryKey(true);

        createTable.addFields(Lists.newArrayList(column));
        UpdateSummary summary = context.executeUpdate(createTable);
    }

    private static void createTableExp1() throws SQLException, ClassNotFoundException {
        UpdateableDataContext context = new JdbcDataContext(getDbConnection());

        // 方案名称或数据库名称
        String schemaName = "public";
        // 表名
        String tableName = "test";

        CreateTable createTable = new CreateTable(schemaName, tableName);
        // 创建主键
        createTable.withColumn("id").ofType(ColumnType.VARCHAR).ofSize(32).asPrimaryKey();
        // 使用数据库本地类型
        createTable.withColumn("name").ofNativeType("VARCHAR").ofSize(16).nullable(false);
        // 使用超类，由metamodel根据数据库类型自动转换成本地类型
        createTable.withColumn("age").ofType(ColumnType.NUMBER).ofSize(6);
        // 精度设置
        createTable.withColumn("property").ofType(ColumnType.NUMERIC).ofSize(6).ofDecimalDigits(2);
        UpdateSummary summary = context.executeUpdate(createTable);
    }

    private static Connection getDbConnection() throws ClassNotFoundException, SQLException {
        Class.forName("org.postgresql.Driver");
        Connection _connection = DriverManager.getConnection("jdbc:postgresql://192.168.0.65:5432/hebei01?gssEncMode=disable&currentSchema=ncloud",
                "gpadmin","xzkingdeejava");
        return _connection;
    }
}
